/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package UserManager;

import DBManager.DBConnection;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

/**
 *
 * @author TigerBoy
 */
public class ProductManager {
    
    private void productManagerLogs(String functionName,String status,String input)
    {
    
        String path = "C:/Foobar_Logs/productLogs.txt";
        
        try
        {
            File file = new File(path);
            FileWriter fw = new FileWriter( file , true);
            BufferedWriter bw = new BufferedWriter(fw);
           
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            java.util.Date date = new java.util.Date();
            String currentDate = dateFormat.format(date);
            
            bw.write("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"); bw.newLine();
            bw.write("Function:" + functionName + " has " + status + ""); bw.newLine();
            bw.write(input); bw.newLine();
            bw.write("Excecution Date and Time: " + currentDate); bw.newLine();
            
            bw.close();
            
        }
        catch (Exception e)
        {
            
            System.out.println(e);
            System.out.println("Writing to the text file: \n" + path + " has failed");
            System.out.println("Please Check if Diskspace is full");
            
        }
    
    }
    
    
    public boolean addProduct(Product newProduct)
    {
        boolean result = false;
        
        String input = "";
        if(newProduct != null)
        {
            try{
                //get connection
                DBConnection db = new DBConnection();
                Connection conn = db.getConnection();

                //Construct SQL Query
                PreparedStatement pstmt1 =
                    conn.prepareStatement("INSERT "
                                    + "INTO product(productType,title,summary,price,sales,stock,status,dateCreated)"
                                    + "values(?,?,?,?,?,?,?,?) ");

                //append values for the parameters


                // for log purposes
                DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                java.util.Date date = new java.util.Date();
                newProduct.setDateCreated(dateFormat.format(date));

                /* for user*/
                int i=1;
                pstmt1.setString(i++, newProduct.getProductType());
                pstmt1.setString(i++, newProduct.getTitle());
                pstmt1.setString(i++, newProduct.getSummary());
                pstmt1.setString(i++, String.valueOf(newProduct.getPrice()));
                pstmt1.setString(i++, String.valueOf(newProduct.getSales()));
                pstmt1.setString(i++, String.valueOf(newProduct.getStock()));
                pstmt1.setString(i++, String.valueOf(newProduct.isStatus()));
                pstmt1.setString(i++, newProduct.getDateCreated());

                //execute SQL statement
                pstmt1.executeUpdate();

                input = "Added product: "+ newProduct.getTitle() + " to " + newProduct.getProductType() + " database with "+ newProduct.getStock() + " stock, with price" + newProduct.getPrice();

                productManagerLogs("addProduct","excecuted", input);

                return true;


            }catch(SQLException ex)
            {

                System.out.println("addProduct() has failed to execute");
                input = "Failed to add product: "+ newProduct.getTitle() + " to " + newProduct.getProductType() + " database with "+ newProduct.getStock() + " stock, with price" + newProduct.getPrice();
                productManagerLogs("addProduct","Failed to Excecute", input);
            }
            
        }
        else
        {
            productManagerLogs("addProduct","Failed to Excecute", "Null entries");
        
        
        }
            
        return false;
        
    }
    
    public boolean discontinueProduct(int productID)
    {
        
        boolean result = false;
        
        if(productID > 0)
        {
            try{
                //get connection
                DBConnection db = new DBConnection();
                Connection conn = db.getConnection();

                //Construct SQL Query
                PreparedStatement pstmt =
                    conn.prepareStatement("UPDATE product SET status=? WHERE productID=?");

                //append values for the parameters
                int i=1;
                pstmt.setString(i++, String.valueOf(0));
                pstmt.setString(i++, String.valueOf(productID));


                //execute SQL statement
                pstmt.executeUpdate();

                productManagerLogs("discontinueProduct","excecuted", productID + " was discontinued");

                result = true;
            }
            catch(SQLException ex){

                System.out.println("discontinueProduct() has failed to execute");

                productManagerLogs("discontinueProduct","Failed to Excecuted", productID + " discontinued failed");
            }
        
        }
        else
        {
            productManagerLogs("discontinueProduct","Failed to Excecuted", "Value of productID is invalid : " + productID);
        
        }
        
        
        return result;
        
    }
    
     public boolean addStock(int productID, int stockAdded)
    {
        
        boolean result = false;
        int stock = 0;
        
        if(stockAdded > 0 && productID > 0)
        {
            
            try{
                //get connection
                DBConnection db = new DBConnection();
                Connection conn = db.getConnection();

                //Construct SQL Query



               // PreparedStatement pstmt1 = conn.prepareStatement("SELECT stock FROM product WHERE productID=?");
                PreparedStatement pstmt2 = conn.prepareStatement("UPDATE product SET stock=? WHERE productID=?");

               // ResultSet rst = pstmt1.executeQuery();

    //            while(rst.next())
    //            {
    //                    stock = rst.getInt("stock");
    //                    
    //            }
    //            
                int i=1;
                pstmt2.setString(i++, String.valueOf(stockAdded ));
                pstmt2.setString(i++, String.valueOf(productID));

                
                
                
                //execute SQL statement
                pstmt2.executeUpdate();
                
                productManagerLogs("addStock","excecuted", productID + " added successfully with stock : " + stockAdded );
                
                result = true;
            }
            catch(SQLException ex){

                System.out.println("addStock() has failed to execute");
                productManagerLogs("addStock","Failed to Excecute", productID + " failed to add");
                
            }
        }
        else
        {
        
            productManagerLogs("addStock","Failed to Excecute", productID + " is either invalid or Stock added was less than 0 which is " + stockAdded);
        
        }
        
        return result;
        
    }
    
    public boolean continueProduct(int productID)
    {
        boolean result = false;
        
        if(productID > 0)
        {
            try{
                //get connection
                DBConnection db = new DBConnection();
                Connection conn = db.getConnection();

                //Construct SQL Query
                PreparedStatement pstmt =
                    conn.prepareStatement("UPDATE product SET status=? WHERE productID=?");

                //append values for the parameters
                int i=1;
                pstmt.setString(i++, String.valueOf(1));
                pstmt.setString(i++, String.valueOf(productID));


                //execute SQL statement
                pstmt.executeUpdate();

                productManagerLogs("continueProduct","excecuted", productID + " was continued");

                result = true;
            }
            catch(SQLException ex){

                System.out.println("continueProduct() has failed to execute");

                productManagerLogs("continueProduct","Failed to Excecuted", productID + " was continued");

            }
        }
        else
        {
            productManagerLogs("continueProduct","Failed to Excecuted", "Value of productID is invalid : " + productID);
        }
        
        return result;
        
    }
    
    public void viewProduct(Product newProduct)
    {
        if(newProduct.getProductID() > 0)
        {
            try{
                //get connection
                DBConnection db = new DBConnection();
                Connection conn = db.getConnection();

                //Construct SQL Query

                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product WHERE productID = '"+ newProduct.getProductID() +"'");

                ResultSet rst = pstmt.executeQuery();



                while(rst.next())
                {
                        newProduct.setProductID(rst.getInt("productID"));
                        newProduct.setProductType(rst.getString("productType"));
                        newProduct.setTitle(rst.getString("title"));
                        newProduct.setSummary(rst.getString("summary"));
                        newProduct.setPrice(rst.getFloat("price"));
                        newProduct.setSales(rst.getInt("sales"));
                        newProduct.setStock(rst.getInt("stock"));
                        newProduct.setStatus(rst.getInt("status"));
                        newProduct.setDateCreated(rst.getString("dateCreated"));

                }

                pstmt.close();

                productManagerLogs("viewProduct","excecuted", newProduct.getProductID() + " was viewed");
                
            }catch(SQLException ex)
            {

                System.out.println("viewProduct() has failed to execute");
                productManagerLogs("viewProduct","Failed to Excecute", newProduct.getProductID() + " value created an error");
            }
        }
        else
        {
            
                productManagerLogs("viewProduct","Failed to Excecute", newProduct.getProductID() + " is invalid");
        }
        
    }
    
    public int numberOfEntries(String keyword, String category)
    {
        if(keyword != null && category != null)
        {
            try{
                //get connection
                DBConnection db = new DBConnection();
                Connection conn = db.getConnection();

                
                //Construct SQL Query
                if("All Categories".equals(category))
                {
                    PreparedStatement pstmt1 = conn.prepareStatement("SELECT * FROM product WHERE title LIKE '%"+ keyword +"%'  AND status = 1");
                    ResultSet rst1 = pstmt1.executeQuery();

                    int numberOfEntries=0;

                    while(rst1.next())
                    {
                        numberOfEntries++;   
                    }

                    pstmt1.close();

                    return numberOfEntries;
                }

                else{
                    PreparedStatement pstmt1 = conn.prepareStatement("SELECT * FROM product WHERE productType = '"+ category +"'and title LIKE '%"+ keyword +"%' AND status = 1");
                    ResultSet rst1 = pstmt1.executeQuery();

                    int numberOfEntries=0;

                    while(rst1.next())
                    {
                        numberOfEntries++;   
                    }

                    pstmt1.close();
                    
                    productManagerLogs("numberOfEntries","Failed to Excecute", "category value: " + category + " and searchProduct value: " +keyword);
                    
                    return numberOfEntries;
                    
                }
                
            }catch(SQLException ex)
            {

                System.out.println("numberOfEntries() has failed to execute");
                productManagerLogs("numberOfEntries","Failed to Excecute", "category value: " + category + " and searchProduct value: " +keyword);
                return 0;
            }
        }
        else
        {
            productManagerLogs("numberOfEntries","Failed to Excecute", category + " (category) has a null value or " + keyword + " (searchProduct) has a null value");
            return 0;
        }
        
    }
    
    public void searchProduct(String keyword,Product newProduct,int size, String category)
    {
            int[] id = new int[size];
            String[] title = new String[size];
            String[] summary = new String[size];
            float[] price = new float[size];
            int[] stock = new int[size];
            String[] type = new String[size];
            int[] status = new int[size];
            
            try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
      if("".equals(keyword))
         newProduct.setCount(0);
            
      else{
                        
            
            if("All Categories".equals(category)){
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product WHERE title LIKE '%"+ keyword +"%' AND status = 1");
            ResultSet rst = pstmt.executeQuery();
            
            int i = 0;
            while(rst.next())
            {
                //place into temporary array
                id[i] = rst.getInt("productID");
                title[i] = rst.getString("title");
                summary[i] = rst.getString("summary");
                price[i] = rst.getFloat("price");
                stock[i] = rst.getInt("stock");
                type[i] = rst.getString("productType");
                status[i] = rst.getInt("status");
                i++;
            }
            
            //from temp array to main array in product class
            newProduct.setProductIDList(id);
            newProduct.setTitleList(title);
            newProduct.setSummaryList(summary);
            newProduct.setPriceList(price);
            newProduct.setStockList(stock);
            newProduct.setTypeList(type);
            newProduct.setStatusList(status);
            newProduct.setCount(size);
            
            pstmt.close();
            }
            
            else{
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product WHERE productType = '"+ category +"'and title LIKE '%"+ keyword +"%' AND status = 1");
            ResultSet rst = pstmt.executeQuery();
            
            int i = 0;
            while(rst.next())
            {
                //place into temporary array
                id[i] = rst.getInt("productID");
                title[i] = rst.getString("title");
                summary[i] = rst.getString("summary");
                price[i] = rst.getFloat("price");
                stock[i] = rst.getInt("stock");
                type[i] = rst.getString("productType");
                status[i] = rst.getInt("status");
                i++;
            }
            
            //from temp array to main array in product class
            newProduct.setProductIDList(id);
            newProduct.setTitleList(title);
            newProduct.setSummaryList(summary);
            newProduct.setPriceList(price);
            newProduct.setStockList(stock);
            newProduct.setTypeList(type);
            newProduct.setStatusList(status);
            newProduct.setCount(size);
            
            pstmt.close();
            }
            
      }
      
        }catch(SQLException ex)
        {
            
            System.out.println("searchProduct() has failed to execute");
        
        }
        
    }
    
    public void viewCategory(String category, Product newProduct, int isDiscontinued)
    {
            int[] id = new int[50];
            String[] title = new String[50];
            String[] summary = new String[50];
            float[] price = new float[50];
            int[] stock = new int[50];
            String[] type = new String[50];
            int[] status = new int[50];
            
            try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            
        if("all".equals(category)){
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product WHERE status=1");
            ResultSet rst = pstmt.executeQuery();
            
            int i = 0;
            while(rst.next())
            {
                //place into temporary array
                id[i] = rst.getInt("productID");
                title[i] = rst.getString("title");
                summary[i] = rst.getString("summary");
                price[i] = rst.getFloat("price");
                stock[i] = rst.getInt("stock");
                type[i] = rst.getString("productType");
                status[i] = rst.getInt("status");
                i++;
            }
            
            //from temp array to main array in product class
            newProduct.setProductIDList(id);
            newProduct.setTitleList(title);
            newProduct.setSummaryList(summary);
            newProduct.setPriceList(price);
            newProduct.setStockList(stock);
            newProduct.setTypeList(type);
            newProduct.setStatusList(status);
            newProduct.setCount(i);
            
            pstmt.close();
            
        }
            
        else if(isDiscontinued == 1) {
            
        
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product WHERE productType = '"+ category +"' AND status = 0");
            ResultSet rst = pstmt.executeQuery();

            
            int i = 0;
            while(rst.next())
            {
                //place into temporary array
                id[i] = rst.getInt("productID");
                title[i] = rst.getString("title");
                summary[i] = rst.getString("summary");
                price[i] = rst.getFloat("price");
                stock[i] = rst.getInt("stock");
                type[i] = rst.getString("productType");
                status[i] = rst.getInt("status");
                i++;
            }
            
            //from temp array to main array in product class
            newProduct.setProductIDList(id);
            newProduct.setTitleList(title);
            newProduct.setSummaryList(summary);
            newProduct.setPriceList(price);
            newProduct.setStockList(stock);
            newProduct.setTypeList(type);
            newProduct.setStatusList(status);
            newProduct.setCount(i);
            
            pstmt.close();

        }            
        
        else{
            
        
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product WHERE productType = '"+ category +"' AND status = 1");
            ResultSet rst = pstmt.executeQuery();

            
            int i = 0;
            while(rst.next())
            {
                //place into temporary array
                id[i] = rst.getInt("productID");
                title[i] = rst.getString("title");
                summary[i] = rst.getString("summary");
                price[i] = rst.getFloat("price");
                stock[i] = rst.getInt("stock");
                type[i] = rst.getString("productType");
                status[i] = rst.getInt("status");
                i++;
            }
            
            //from temp array to main array in product class
            newProduct.setProductIDList(id);
            newProduct.setTitleList(title);
            newProduct.setSummaryList(summary);
            newProduct.setPriceList(price);
            newProduct.setStockList(stock);
            newProduct.setTypeList(type);
            newProduct.setStatusList(status);
            newProduct.setCount(i);
            
            pstmt.close();

        }            
            
        }catch(SQLException ex)
        {
            
            System.out.println("viewCategory() has failed to execute");
        
        }
        
    }
    
    public boolean changePrice(int productID, float newPrice)
    {
        
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE product SET price=? WHERE productID=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, String.valueOf(newPrice));
            pstmt.setString(i++, String.valueOf(productID));
            
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
        
            System.out.println("changePrice() has failed to execute");
            
            
        }
        
        return result;
        
    }
	
    public boolean changeSummary(int productID, String newSummary)
    {
        
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE product SET summary=? WHERE productID=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, newSummary);
            pstmt.setString(i++, String.valueOf(productID));
            
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
        
            System.out.println("changeSummary() has failed to execute");
            
            
        }
        
        return result;
        
    }
	
    public boolean changeTitle(int productID, String newTitle)
    {
        
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE product SET title=? WHERE productID=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, newTitle);
            pstmt.setString(i++, String.valueOf(productID));
            
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
        
            System.out.println("changeTitle() has failed to execute");
            
            
        }
        
        return result;
        
    }
    
    
    /*
     * result = 1 --> Successful
     * result = 2 --> Credit Card does not exist
     * result = 3 --> No product exists
     * result = 4 --> No User exists
     * result = 5 --> Credit Card is expired
     * result = 6 --> Stock is not 0, but is less than quantity
     * result = 7 --> Stock is 0
     * result = 8 --> Quantity is negative (user validation)
     * result = 9 --> Total Function Failure
     * result = 10 --> User is not Active
     * result = 11 --> User is blocked by Admin
     * result = 12 --> Quantity <1
     */
    public int purchaseProduct(Transaction newTransaction)
    {
        
         try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn0 = db.getConnection();
            Connection conn = db.getConnection();
            Connection conn2 = db.getConnection();
            
         
            PreparedStatement pstmt0 = conn0.prepareStatement("Select * FROM creditcard WHERE creditNumber=?");
            PreparedStatement pstmt1 = conn.prepareStatement("Select * FROM product WHERE productID=?");
            PreparedStatement pstmt2 = conn.prepareStatement("Select * FROM user WHERE username=?");
            
            int i;
            
            i=1;
            pstmt0.setString(i++, newTransaction.getCreditNumber());
            ResultSet rst0 = pstmt0.executeQuery();
            
            CreditCard newCard = new CreditCard();
            
            int checker=0;
            
            while(rst0.next())
            {
                newCard.setCreditName(rst0.getString("creditName"));
                newCard.setCreditNumber(rst0.getString("creditNumber"));
                newCard.setCreditYear(rst0.getInt("creditYear"));
                newCard.setCreditMonth(rst0.getInt("creditMonth"));
                newCard.setCreditCompany(rst0.getString("creditCompany"));
                newCard.setAddressBillNumber(rst0.getString("addressBillNumber"));
                newCard.setAddressBillStreet(rst0.getString("addressBillStreet"));
                newCard.setAddressBillSubdivision(rst0.getString("addressBillSubdivision"));
                newCard.setAddressBillCity(rst0.getString("addressBillCity"));
                newCard.setAddressBillPostal(rst0.getString("addressBillPostal"));
                newCard.setAddressBillCountry(rst0.getString("addressBillCountry"));
                checker++;
            }
            CreditCardManager verify = new CreditCardManager();
            
            
            
            if( checker < 1 ) {return 2;}
            if(!verify.verifyCardNumber(newCard)) {return 5;}
            
            i=1;
            pstmt1.setString(i++, String.valueOf(newTransaction.getProductID()));
            i=1;
            pstmt2.setString(i++, newTransaction.getUserID());
            
            
            
            ResultSet rst1 = pstmt1.executeQuery();
            ResultSet rst2 = pstmt2.executeQuery();
            
            
            
            // Checks if product Exists
            int productID = 0;
            int stock = 0;
            int status;
            while(rst1.next())
            {
                productID = rst1.getInt("productID");
                stock = rst1.getInt("stock");
                status = rst1.getInt("status");
            }
            if(productID == 0) {return 3;}
            // Checks if product Exists
            
            // Checks for username if he exists
            String username = "";
            int lockUser=1;
            int isActive=0;
            while(rst2.next())
            {
                username = rst2.getString("username");
                isActive = rst2.getInt("isActive");
                lockUser = rst2.getInt("lockUser");
            }
            if(username.equals("")) {return 4;}
            // Checks for username if he exists
            
            conn2.setAutoCommit(false);
            PreparedStatement pstmt3 = conn2.prepareStatement("UPDATE product SET stock=? WHERE productID=?");
            PreparedStatement pstmt4 = 
                    conn2.prepareStatement("INSERT "
                    + "INTO transaction(userID,creditNumber,productID,productType,price,quantity,dateCreated)"
                    + "values(?,?,?,?,?,?,?) ");
            
            if(stock < newTransaction.getQuantity()) {return 6;}
            if(stock == 0) {return 7;}
            if(newTransaction.getQuantity() < 0) {return 8;}
            if(isActive == 0) {return 10;}
            if(lockUser == 1) {return 11;}
            if(newTransaction.getQuantity()<1) {return 12;}
            
            i=1;
            pstmt3.setString(i++, String.valueOf(stock-newTransaction.getQuantity()));
            pstmt3.setString(i++, String.valueOf(newTransaction.getProductID()));
            
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            java.util.Date date = new java.util.Date();
            newTransaction.setDateCreated(dateFormat.format(date));
 
            
            i=1;
            pstmt4.setString(i++, newTransaction.getUserID() );
            pstmt4.setString(i++, newTransaction.getCreditNumber() );
            pstmt4.setString(i++, String.valueOf(newTransaction.getProductID()) );
            pstmt4.setString(i++, newTransaction.getProductType() );
            pstmt4.setString(i++, String.valueOf(newTransaction.getPrice()) );
            pstmt4.setString(i++, String.valueOf(newTransaction.getQuantity()) );
            pstmt4.setString(i++, newTransaction.getDateCreated() );
            
            pstmt3.executeUpdate();
            pstmt4.executeUpdate();
            
            conn2.commit();
            
            pstmt1.close();
            pstmt2.close();
            pstmt3.close();
            pstmt4.close();
            
            return 1;
            
         }catch(SQLException ex)
         {
            
             System.out.println("purchaseProduct() has failed to execute");
        
         }
        
        
        
        return 9;
    }
    
    /*
     * result = 1 --> Success
     * result = 2 --> Function Failure
     * result = 3 --> User hasn't purchased the product
     * result = 4 --> User has already reviewed
     */
    public int addComment(Review newReview)
    {
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt1 =
                conn.prepareStatement("INSERT "
                                + "INTO reviews(productID,userID,rating,comment,dateCreated)"
                                + "values(?,?,?,?,?) ");
            
            PreparedStatement pstmt2 =
                    conn.prepareStatement("SELECT * FROM transaction WHERE productID=? AND userID=?");
            
            PreparedStatement pstmt3 =
                    conn.prepareStatement("SELECT * FROM reviews WHERE productID=? AND userID=?");
            
            
            // for log purposes
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            java.util.Date date = new java.util.Date();
            String currentDate = dateFormat.format(date);
            
            
            /* for user*/
            int i=1;
            pstmt1.setString(i++, String.valueOf(newReview.getProductID()));
            pstmt1.setString(i++, newReview.getUserID());
            pstmt1.setString(i++, String.valueOf(newReview.getRating()));
            pstmt1.setString(i++, newReview.getComment());
            pstmt1.setString(i++, currentDate);
             
            i=1;
            pstmt2.setString(i++, String.valueOf(newReview.getProductID()));
            pstmt2.setString(i++, newReview.getUserID());
            ResultSet rst = pstmt2.executeQuery();
            
            
            
            int checkerIfBought = 0;
            
            while(rst.next())
            {
                checkerIfBought++;
            }
            
            
            i=1;
            pstmt3.setString(i++, String.valueOf(newReview.getProductID()));
            pstmt3.setString(i++, newReview.getUserID());
            ResultSet rst2 = pstmt3.executeQuery();
            
            
            
            int checkIfReviewed = 0;
            
            while(rst2.next())
            {
                checkIfReviewed++;
            }
            
            
            //execute SQL statement
            if(checkIfReviewed >= 1)
            {
                
                pstmt1.close();
                pstmt2.close();
                pstmt3.close();
                return 4;
            }
            if(checkerIfBought >= 1)
            {
                
                
                pstmt1.executeUpdate();
                pstmt1.close();
                pstmt2.close();
                pstmt3.close();
                return 1;
            }
            else
            {
                pstmt1.close();
                pstmt2.close();
                pstmt3.close();
                return 3;
            }
            
        }catch(SQLException ex)
        {
            
            System.out.println("addComment() has failed to execute");
        
        }
        
            
        return 2;
        
    }
    
    public int numberOfEntriesForViewComment(int productID)
    {
        int counter = 0;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
      
            if(productID<0)
            {
                return 0;
            }
      
            
            PreparedStatement pstmt1 = conn.prepareStatement("SELECT * FROM reviews WHERE productID=?");
            
            int i = 1;
            pstmt1.setString(i++, String.valueOf(productID));
            ResultSet rst1 = pstmt1.executeQuery();

            while(rst1.next())
            {
                counter++;   
            }

            pstmt1.close();

            return counter;
            
            
            
        }catch(SQLException ex)
        {
            
            System.out.println("numberOfEntriesForViewComment() has failed to execute");
        
        }
        
    
        return counter;
    }
    
    public boolean viewComment(int productIDmain,Review newReview)
    {
        
        int counter = numberOfEntriesForViewComment(productIDmain);
        
        int[] productID = new int[counter];
        String[] username = new String[counter];
        int[] rating = new int[counter];
        String[] comment = new String[counter];
        String[] dateCreated = new String[counter];
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
      
            if(productIDmain<0)
            {
                
                return false;
            }
      
            
            PreparedStatement pstmt1 = conn.prepareStatement("SELECT * FROM reviews WHERE productID=?");
            
            int i = 1;
            pstmt1.setString(i++, String.valueOf(productIDmain));
            ResultSet rst1 = pstmt1.executeQuery();

            int ctr=0;
            
            while(rst1.next())
            {
                productID[ctr] = rst1.getInt("productID");
                username[ctr] = rst1.getString("userID");
                rating[ctr] = rst1.getInt("rating");
                comment[ctr] = rst1.getString("comment");
                dateCreated[ctr] = rst1.getString("dateCreated");
                ctr++;
            }

            
            newReview.setProductIDList(productID);
            newReview.setUserIDList(username);
            newReview.setRatingList(rating);
            newReview.setCommentList(comment);
            newReview.setDateCreatedList(dateCreated);
            newReview.setCount(ctr);
            
            
            
            pstmt1.close();

            return true;
            
            
            
        }catch(SQLException ex)
        {
            
            System.out.println("numberOfEntriesForViewComment() has failed to execute");
        
        }
            
        return false;
    }
    
    
     public int numberOfTransactions()
    {
        int counter = 0;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
      
            
            PreparedStatement pstmt1 = conn.prepareStatement("SELECT * FROM transaction");
            ResultSet rst1 = pstmt1.executeQuery();
            while(rst1.next())
            {
                counter++;   
            }

            pstmt1.close();

            return counter;
            
            
            
        }catch(SQLException ex)
        {
            
            System.out.println("numberOfTransactionst() has failed to execute");
        
        }
        
    
        return counter;
    }
    
    
    
    public boolean viewTransactions(Transaction newTransaction)
    {
        
        int counter = numberOfTransactions();
        int[] transactions = new int[counter];
        String[] productnames = new String[counter];
        String[] username = new String[counter];
        float[] price = new float[counter];
        int[] quantity = new int[counter];
        String[] dateCreated = new String[counter];
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
      
   
            
            PreparedStatement pstmt1 = conn.prepareStatement("SELECT * FROM transaction");
            
            int i = 1;
            ResultSet rst1 = pstmt1.executeQuery();

            int ctr=0;
            
            while(rst1.next())
            {
                transactions[ctr] = rst1.getInt("transactionID");
                username[ctr] = rst1.getString("userID");
                price[ctr] = rst1.getFloat("price");
                quantity[ctr] = rst1.getInt("quantity");
                dateCreated[ctr] = rst1.getString("dateCreated");
                productnames[ctr] = viewProductName(rst1.getInt("productID"));
                ctr++;
            }

            newTransaction.setTransactionIDList(transactions);
            newTransaction.setUserList(username);
            newTransaction.setPriceList(price);
            newTransaction.setQuantityList(quantity);
            newTransaction.setDateList(dateCreated);
            newTransaction.setProductList(productnames);
            newTransaction.setCount(ctr);
            
            
            
            
            pstmt1.close();

            return true;
            
            
            
        }catch(SQLException ex)
        {
            
            System.out.println("viewTransactions() has failed to execute");
        
        }
            
        return false;
    }
    
    public String viewProductName(int productID)
    {
        
        String productname = new String();
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
      
   
            
            PreparedStatement pstmt1 = conn.prepareStatement("SELECT title FROM product WHERE productID=?");
            
            int i = 1;
            pstmt1.setString(i++, String.valueOf(productID));
            ResultSet rst1 = pstmt1.executeQuery();

            int ctr=0;
            
            while(rst1.next())
            {
                productname = rst1.getString("title");
                ctr++;
            }
            pstmt1.close();
            return productname;
            
            
            
            

            
            
            
        }catch(SQLException ex)
        {
            
            System.out.println("viewProductName() has failed to execute");
        
        }
           return productname; 
    }
    
    
    
}
